﻿Imports System.Data.SqlClient
Public Class FormasPgtoDAL
#Region "Incluir"
    Public Function inserir(ByVal pagdescricao As String, ByVal stacodigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "insert into Generico.Gen_FormaPagamento(pag_descricao,sta_codigo) values(@descricao,@status)"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = pagdescricao
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = stacodigo
            codigo = objconexao.executacmdAI(cmd, "Gen_FormaPagamento")
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#Region "Alterar"
    Public Function alterar(ByVal pagcodigo As Integer, ByVal stacodigo As Integer, ByVal pagdescricao As String) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandText = "Update Generico.Gen_FormaPagamento set sta_codigo = @status, pag_descricao=@descricao where pag_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = pagcodigo
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = stacodigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = pagdescricao
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
#Region "Excluir"
    Function Excluir(ByRef codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "update Generico.Gen_FormaPagamento set sta_codigo=3 where pag_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region
#Region "Localizar"
    Function localiza(ByVal codigo As Integer) As DataTable 'Por Código
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Generico.Gen_FormaPagamento where pag_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizaGeral() As DataTable 'ok
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FormaPagamento where sta_codigo <> 3"
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizarDescricao(descricao As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from VWFormaPagamento where pag_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao and sta_codigo <> 3"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & descricao & "%"
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizaStatus(status As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from VWFormaPagamento where sta_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = status
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

#End Region
#Region "Buscar Filtro"
    Public Function buscar(ByVal Descricao As String, filtro As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case filtro
                Case 0
                    cmd.CommandText = "select * from vw_FormaPagamento where pag_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_FormaPagamento where pag_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from vw_FormaPagamento where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao and sta_codigo <> 3"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"

            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Valida" 'será utilizado no segundo semestre para validar outros status (pago, em aberto, etc)
    Function validaPgtoCtReceber(ByVal cod As Integer) As Boolean 'Ctas a Receber
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Ofi_ContasReceber where pag_codigo = @codigo and sta_codigo <> 4 "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function validaCtPagar(ByVal cod As Integer) As Boolean 'Ctas a Pagar
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Ofi_ContasPagar where pag_codigo = @codigo and sta_codigo <> 4"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region

End Class
